﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ZhimeiSoft.CharmManager.Server.DAL
{
    public class Reservation_Const
    {
        public static readonly string SQL_INSERT_RESERVATION = @"INSERT INTO RESERVATION(RESERVATIONTIME,RWORKSTARTTIME,RWORKENDTIME,RSTATE,RMEMO,CUSTOMERID) VALUES 
            (@RESERVATIONTIME,@RWORKSTARTTIME,@RWORKENDTIME,@RSTATE,@RMEMO,@CUSTOMERID)";

        public static readonly string SQL_UPDATE_RESERVATION = @"UPDATE RESERVATION SET RESERVATIONTIME = @RESERVATIONTIME, RWORKSTARTTIME = @RWORKSTARTTIME,
            RWORKENDTIME = @RWORKENDTIME, RSTATE = @RSTATE, RMEMO = @RMEMO,CUSTOMERID = @CUSTOMERID WHERE RESERVATIONID = @RESERVATIONID";

        public static readonly string SQL_DELETE_RESERVATION = "DELETE FROM RESERVATION WHERE RESERVATIONID = @RESERVATIONID";

        public static readonly string SQL_DELETE_RESERVATIONSERVICEITEM = "DELETE FROM RESERVATIONSERVICEITEM WHERE RESERVATIONID = @RESERVATIONID";

        public static readonly string SQL_DELETE_TODOLIST = "DELETE FROM TODOLIST WHERE TODOLISTID IN (SELECT TODOLISTID FROM RESVERATIONTODOLIST WHERE RESERVATIONID = @RESERVATIONID)";

        public static readonly string SQL_DELETE_RESVERATIONTODOLIST = "DELETE FROM RESVERATIONTODOLIST WHERE RESERVATIONID = @RESERVATIONID";

        public static readonly string SQL_DELETE_RESERVATIONSERVICEITEM_DETAIL = "DELETE FROM RESERVATIONSERVICEITEM WHERE RESERVATIONID = @RESERVATIONID AND CUSTOMERSERVICEITEMID = @CUSTOMERSERVICEITEMID";

        public static readonly string SQL_UPDATE_RESERVATION_RESERVATIONTIME = @"UPDATE RESERVATION SET RESERVATIONTIME = @RESERVATIONTIME, RWORKSTARTTIME = @RWORKSTARTTIME, RWORKENDTIME = @RWORKENDTIME 
            , RMEMO = @RMEMO  WHERE RESERVATIONID = @RESERVATIONID";

        public static readonly string SQL_UPDATE_RESERVATIONSERVICEITEM_ADJ = @"UPDATE RESERVATIONSERVICEITEM  SET RESERVATIONID = @TRAGETRESERVATIONID WHERE RESERVATIONID = @SOURCERESERVATIONID ";

        public static readonly string SQL_UPDATE_RESERVATIONSERVICEITEM_STATE = @"UPDATE RESERVATIONSERVICEITEM SET RESERVATIONSTATE = @RESERVATIONSTATE, EMPLOYEEID = @EMPLOYEEID
                    WHERE RESERVATIONID = @RESERVATIONID AND CUSTOMERSERVICEITEMID = @CUSTOMERSERVICEITEMID";

        public static readonly string SQL_UPDATE_RESERVATIONSERVICEITEM_EMPLOYEEID = @"UPDATE RESERVATIONSERVICEITEM SET EMPLOYEEID = @EMPLOYEEID WHERE RESERVATIONID = @RESERVATIONID ";

        public static readonly string SQL_GET_RESERVATION = @"SELECT RESERVATIONID FROM RESERVATION WHERE  YEAR(RESERVATIONTIME) = @QUERYYEAR AND MONTH(RESERVATIONTIME) = @QUERYMONTH AND DAY(RESERVATIONTIME) = @QUERYDAY AND CUSTOMERID = @CUSTOMERID";

        public static readonly string SQL_QUERY_RESERVATION_MONTH = @"SELECT RESERVATION.RESERVATIONID, RESERVATION.RESERVATIONTIME, RESERVATION.RWORKSTARTTIME, RESERVATION.RWORKENDTIME, RESERVATION.RSTATE, RESERVATION.RMEMO AS SMEMO,  RESERVATION.RMEMO, 
            CUSTOMINFO.CUSTOMERID, CUSTOMINFO.CNAME, CUSTOMINFO.CNICKNAME + '   ' + CONVERT(varchar(12) , RESERVATION.RESERVATIONTIME, 108 ) AS CNICKNAME, CUSTOMINFO.CMOBILEPHONE 
            FROM RESERVATION, CUSTOMINFO WHERE RESERVATION.CUSTOMERID = CUSTOMINFO.CUSTOMERID AND YEAR(RESERVATIONTIME) = @QRYYEAR AND MONTH(RESERVATIONTIME) = @QRYMONTH";


        public static readonly string SQL_QUERY_RESERVATION_MONTH_STATE = @"SELECT RESERVATION.RESERVATIONID, RESERVATION.RESERVATIONTIME, RESERVATION.RWORKSTARTTIME, RESERVATION.RWORKENDTIME, RESERVATION.RSTATE, RESERVATION.RMEMO AS SMEMO,  RESERVATION.RMEMO, 
            CUSTOMINFO.CUSTOMERID, CUSTOMINFO.CNAME, CUSTOMINFO.CNICKNAME + '   ' + CONVERT(varchar(12) , RESERVATION.RESERVATIONTIME, 108 ) AS CNICKNAME, CUSTOMINFO.CMOBILEPHONE 
            FROM RESERVATION, CUSTOMINFO WHERE RESERVATION.CUSTOMERID = CUSTOMINFO.CUSTOMERID ";

        public static readonly string SQL_QUERY_RESERVATION_TIME = @"SELECT * FROM RESERVATION WHERE YEAR(A.RESERVATIONTIME) = @QUERYYEAR AND MONTH(A.RESERVATIONTIME) = @QUERYMONTH AND DAY(A.RESERVATIONTIME) = @QUERYDAY";

        public static readonly string SQL_QUERY_RESERVATION_CUSTOMINFO_TIME = @"SELECT A.*, B.* FROM RESERVATION A, CUSTOMINFO B 
                WHERE YEAR(A.RESERVATIONTIME) = @QUERYYEAR  AND MONTH(A.RESERVATIONTIME) = @QUERYMONTH 
                AND DAY(A.RESERVATIONTIME) = @QUERYDAY AND A.CUSTOMERID = B.CUSTOMERID";

        public static readonly string SQL_QUERY_RESERVATION_CUSTOMINFO_TIME_STATE = @"SELECT A.*, B.* FROM RESERVATION A, CUSTOMINFO B 
                WHERE YEAR(A.RESERVATIONTIME) = @QUERYYEAR  AND MONTH(A.RESERVATIONTIME) = @QUERYMONTH AND DAY(A.RESERVATIONTIME) = @QUERYDAY AND A.RSTATE = @RSTATE AND A.CUSTOMERID = B.CUSTOMERID";

        public static readonly string SQL_QUERY_RESERVATION_RESERVATIONSERVICEITEM_EMPLOYEE = @"SELECT A.*, B.* FROM RESERVATION A, RESERVATIONSERVICEITEM B WHERE A.RESERVATIONID = B.RESERVATIONID AND 
            YEAR(A.RESERVATIONTIME) = @QUERYYEAR  AND MONTH(A.RESERVATIONTIME) = @QUERYMONTH  AND DAY(A.RESERVATIONTIME) = @QUERYDAY AND B.EMPLOYEEID = @EMPLOYEEID";

        public static readonly string SQL_QUERY_RESERVATION_RESERVATIONSERVICEITEM_CUSTOMINFO_STATE = @"SELECT A.*, B.* FROM RESERVATION A, RESERVATIONSERVICEITEM B WHERE A.RESERVATIONID = B.RESERVATIONID AND 
            YEAR(A.RESERVATIONTIME) = @QUERYYEAR  AND MONTH(A.RESERVATIONTIME) = @QUERYMONTH  AND DAY(A.RESERVATIONTIME) = @QUERYDAY AND A.RSTATE = @RSTATE AND B.EMPLOYEEID = @EMPLOYEEID";

        public static readonly string SQL_QUERY_RESERVATION_RESERVATIONSERVICEITEM_NULLEMPLOYEE = @"SELECT A.*, B.* FROM RESERVATION A, RESERVATIONSERVICEITEM B WHERE A.RESERVATIONID = B.RESERVATIONID AND 
            YEAR(A.RESERVATIONTIME) = @QUERYYEAR  AND MONTH(A.RESERVATIONTIME) = @QUERYMONTH  AND DAY(A.RESERVATIONTIME) = @QUERYDAY";

        public static readonly string SQL_QUERY_RESERVATION_NULLEMPLOYEE_RESERVATIONSERVICEITEM_STATE = @"SELECT A.*, B.* FROM RESERVATION A, RESERVATIONSERVICEITEM B WHERE A.RESERVATIONID = B.RESERVATIONID AND 
            YEAR(A.RESERVATIONTIME) = @QUERYYEAR  AND MONTH(A.RESERVATIONTIME) = @QUERYMONTH  AND DAY(A.RESERVATIONTIME) = @QUERYDAY AND A.RSTATE = @RSTATE ";

        public static readonly string SQL_QUERY_RESERVATIONSERVICEITEM_SERVICEITEM = @"SELECT A.*, B.*, C.* FROM RESERVATIONSERVICEITEM A , CUSTOMERSERVICEITEM B, SERVICEITEM C
            WHERE A.RESERVATIONID = @RESERVATIONID AND A.CUSTOMERSERVICEITEMID = B.CUSTOMERSERVICEITEMID AND B.SERVICEITEMID = C.SERVICEITEMID";

        public static readonly string SQL_QUERY_RESERVATION_ID = "SELECT A.*, B.* FROM RESERVATION A, CUSTOMINFO B WHERE A.CUSTOMERID = B.CUSTOMERID AND A.RESERVATIONID = @RESERVATIONID";

        public static readonly string SQL_QUERY_RESERVATION_CUSTOM = @"SELECT * FROM RESERVATION 
            WHERE YEAR(RESERVATIONTIME) = @QUERYYEAR  AND MONTH(RESERVATIONTIME) = @QUERYMONTH  AND DAY(RESERVATIONTIME) = @QUERYDAY AND CUSTOMERID = @CUSTOMERID";
        
        public static readonly string SQL_QUERY_RESERVATIONDETAIL_CUSTOM = @"SELECT * FROM RESERVATIONSERVICEITEM 
            WHERE RESERVATIONID IN ( SELECT RESERVATIONID FROM RESERVATION WHERE YEAR(RESERVATIONTIME) = @QUERYYEAR  AND MONTH(RESERVATIONTIME) = @QUERYMONTH  AND DAY(RESERVATIONTIME) = @QUERYDAY AND CUSTOMERID = @CUSTOMERID )";

        public static readonly string SQL_QUERY_RESERVATION_CUSTOMSERVICEITEM = @"SELECT A.RESERVATIONID, A.RESERVATIONTIME, A.RSTATE,A.CUSTOMERID,B.CUSTOMERSERVICEITEMID, B.EMPLOYEEID, B.CUSTOMERSERVICEITEMID
                FROM RESERVATION A, RESERVATIONSERVICEITEM B WHERE A.RESERVATIONID = B.RESERVATIONID AND A.RSTATE <> 'C0A' AND A.CUSTOMERID = @CUSTOMERID AND B.CUSTOMERSERVICEITEMID = @CUSTOMERSERVICEITEMID";

        public static readonly string SQL_QUERY_RESERVATION_CUSTOMSERVICEITEM_FILTER = @"SELECT A.RESERVATIONID, A.RESERVATIONTIME, A.RSTATE,A.CUSTOMERID,B.CUSTOMERSERVICEITEMID, B.EMPLOYEEID, B.CUSTOMERSERVICEITEMID
                FROM RESERVATION A, RESERVATIONSERVICEITEM B WHERE A.RESERVATIONID = B.RESERVATIONID ";

        public static readonly string SQL_QUERY_RESERVATIONSERVICEITEM_RESERVATIONID = @"SELECT A.*, B.*, C.* FROM RESERVATIONSERVICEITEM A, CUSTOMERSERVICEITEM B, SERVICEITEM C
            WHERE A.CUSTOMERSERVICEITEMID = B.CUSTOMERSERVICEITEMID AND B.SERVICEITEMID = C.SERVICEITEMID AND A.RESERVATIONID = @RESERVATIONID";

        public static readonly string SQL_UPDATE_RESERVATIONSTATE = "UPDATE RESERVATION SET RSTATE = @RSTATE  WHERE RESERVATIONTIME < @RESERVATIONTIME";

        public static readonly string SQL_UPDATE_RESERVATIONSTATE_RESERVATIONID = "UPDATE RESERVATION SET RSTATE = @RSTATE  WHERE RESERVATIONID = @RESERVATIONID AND RSTATE <> 'C0A'";

        public static string SQL_QUERY_RESERVATIONSERVICEITEM_FORRESERVATIONID = "SELECT * FROM  RESERVATIONSERVICEITEM WHERE RESERVATIONID = @RESERVATIONID";
    }
}
